package com.sergio.accounting.repository;

import com.sergio.accounting.entity.Record;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

/**
 * 记账记录数据访问层
 */
@Repository
public interface RecordRepository extends JpaRepository<Record, Long> {

    /**
     * 根据用户ID查找记录
     */
    @Query("SELECT r FROM Record r LEFT JOIN FETCH r.account WHERE r.userId = :userId ORDER BY r.recordDate DESC")
    List<Record> findByUserIdOrderByRecordDateDesc(@Param("userId") Long userId);

    /**
     * 根据用户ID查找所有记录（不排序，用于统计）
     */
    @Query("SELECT r FROM Record r LEFT JOIN FETCH r.account WHERE r.userId = :userId")
    List<Record> findByUserId(@Param("userId") Long userId);

    /**
     * 根据用户ID和记录类型查找记录
     */
    @Query("SELECT r FROM Record r LEFT JOIN FETCH r.account WHERE r.userId = :userId AND r.recordType = :recordType ORDER BY r.recordDate DESC")
    List<Record> findByUserIdAndRecordTypeOrderByRecordDateDesc(@Param("userId") Long userId, @Param("recordType") Record.RecordType recordType);

    /**
     * 根据用户ID和日期范围查找记录
     */
    @Query("SELECT r FROM Record r LEFT JOIN FETCH r.account WHERE r.userId = :userId AND r.recordDate BETWEEN :startDate AND :endDate ORDER BY r.recordDate DESC")
    List<Record> findByUserIdAndDateRange(@Param("userId") Long userId, 
                                        @Param("startDate") LocalDate startDate, 
                                        @Param("endDate") LocalDate endDate);

    /**
     * 根据用户ID、记录类型和日期范围查找记录
     */
    @Query("SELECT r FROM Record r LEFT JOIN FETCH r.account WHERE r.userId = :userId AND r.recordType = :recordType AND r.recordDate BETWEEN :startDate AND :endDate ORDER BY r.recordDate DESC")
    List<Record> findByUserIdAndTypeAndDateRange(@Param("userId") Long userId, 
                                               @Param("recordType") Record.RecordType recordType,
                                               @Param("startDate") LocalDate startDate, 
                                               @Param("endDate") LocalDate endDate);

    /**
     * 根据用户ID和分类ID查找记录
     */
    List<Record> findByUserIdAndCategoryIdOrderByRecordDateDesc(Long userId, Long categoryId);

    /**
     * 统计用户指定日期范围内的收入总额
     */
    @Query("SELECT COALESCE(SUM(r.amount), 0) FROM Record r WHERE r.userId = :userId AND r.recordType = 'INCOME' AND r.recordDate BETWEEN :startDate AND :endDate")
    BigDecimal sumIncomeByUserIdAndDateRange(@Param("userId") Long userId, 
                                           @Param("startDate") LocalDate startDate, 
                                           @Param("endDate") LocalDate endDate);

    /**
     * 统计用户指定日期范围内的支出总额
     */
    @Query("SELECT COALESCE(SUM(r.amount), 0) FROM Record r WHERE r.userId = :userId AND r.recordType = 'EXPENSE' AND r.recordDate BETWEEN :startDate AND :endDate")
    BigDecimal sumExpenseByUserIdAndDateRange(@Param("userId") Long userId, 
                                            @Param("startDate") LocalDate startDate, 
                                            @Param("endDate") LocalDate endDate);

    /**
     * 按分类统计用户指定日期范围内的金额
     */
    @Query("SELECT r.category.id, r.category.name, r.category.icon, SUM(r.amount) as totalAmount " +
           "FROM Record r WHERE r.userId = :userId AND r.recordType = :recordType AND r.recordDate BETWEEN :startDate AND :endDate " +
           "GROUP BY r.category.id, r.category.name, r.category.icon ORDER BY totalAmount DESC")
    List<Object[]> getCategoryStatsByUserIdAndTypeAndDateRange(@Param("userId") Long userId, 
                                                             @Param("recordType") Record.RecordType recordType,
                                                             @Param("startDate") LocalDate startDate, 
                                                             @Param("endDate") LocalDate endDate);

    /**
     * 按日期统计用户指定日期范围内的收支情况
     */
    @Query("SELECT r.recordDate, " +
           "COALESCE(SUM(CASE WHEN r.recordType = 'INCOME' THEN r.amount ELSE 0 END), 0) as income, " +
           "COALESCE(SUM(CASE WHEN r.recordType = 'EXPENSE' THEN r.amount ELSE 0 END), 0) as expense " +
           "FROM Record r WHERE r.userId = :userId AND r.recordDate BETWEEN :startDate AND :endDate " +
           "GROUP BY r.recordDate ORDER BY r.recordDate")
    List<Object[]> getDailyStatsByUserIdAndDateRange(@Param("userId") Long userId, 
                                                    @Param("startDate") LocalDate startDate, 
                                                    @Param("endDate") LocalDate endDate);

    /**
     * 统计用户记录总数
     */
    long countByUserId(Long userId);

    /**
     * 统计用户指定日期范围内的记录数
     */
    long countByUserIdAndRecordDateBetween(Long userId, LocalDate startDate, LocalDate endDate);

    /**
     * 查找用户最大金额记录
     */
    @Query("SELECT r FROM Record r WHERE r.userId = :userId ORDER BY r.amount DESC")
    List<Record> findTopByUserIdOrderByAmountDesc(@Param("userId") Long userId);

    /**
     * 根据账户ID查找记录
     */
    @Query("SELECT r FROM Record r WHERE r.account.id = :accountId AND r.userId = :userId ORDER BY r.recordDate DESC, r.createdAt DESC")
    List<Record> findByAccountIdAndUserIdOrderByRecordDateDesc(@Param("accountId") Long accountId, @Param("userId") Long userId);

    /**
     * 根据账户ID和日期范围查找记录
     */
    @Query("SELECT r FROM Record r WHERE r.account.id = :accountId AND r.userId = :userId AND r.recordDate BETWEEN :startDate AND :endDate ORDER BY r.recordDate DESC, r.createdAt DESC")
    List<Record> findByAccountIdAndUserIdAndDateRange(@Param("accountId") Long accountId, 
                                                    @Param("userId") Long userId,
                                                    @Param("startDate") LocalDate startDate, 
                                                    @Param("endDate") LocalDate endDate);
}
